﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace homework2
{
    public class DataBaseTool
    {
        string connString = "server=localhost;database=Student;uid=288a;pwd=genghe020524";
        private SqlConnection conn = null;

        /// <summary>
        /// 连接数据库
        /// </summary>
        /// <returns></returns>
        public bool ConnDB()
        {
            conn = new SqlConnection(connString);
            if (conn.State == ConnectionState.Closed)
            {
                try
                {
                    conn.Open();
                }
                catch (SqlException)
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns></returns>
        public bool CloseDB()
        {
            if(conn.State==ConnectionState.Open)
            {
                try
                {
                    conn.Close();
                    return true;
                }
                catch (SqlException)
                {
                    return false;
                }
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 登录验证函数
        /// </summary>
        /// <param name="sno"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool login(String sno, String password)
        {
            if (!ConnDB())
                return false;
            List<student> list = new List<student>();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from [csharpUser] where sno = '" + sno + "' and password = '" + password + "'";
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                student student = new student();
                student.set_sno(dr["sno"].ToString());
                student.set_password(dr["password"].ToString());
                list.Add(student);
            }

            if (!CloseDB())
                return false;
            return list.Count != 0;
        }

        /// <summary>
        /// 注册函数
        /// </summary>
        /// <param name="sno"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public bool register(string sno, string password)
        {
            if (!ConnDB())
                return false;
            // 验证是否学号已被注册
            List<student> list = new List<student>();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "select * from [csharpUser] where sno = '" + sno + "'";
            SqlDataReader dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                student student = new student();
                student.set_sno(dr["sno"].ToString());
                student.set_password(dr["password"].ToString());
                list.Add(student);
            }
            dr.Close();
            if (list.Count != 0)
            {
                CloseDB();
                return false;
            }
            
            // 插入
            //执行SQL命令
            cmd.CommandText = "insert into [csharpUser] values('" + sno + "', '" + password + "')";
            cmd.ExecuteNonQuery();

            return CloseDB();
        }

        /// <summary>
        /// 更新显示列表
        /// </summary>
        /// <param name="sno"></param>
        /// <returns></returns>
        public DataTable getDataSource(string sno)
        {
            ConnDB();
            string sql = "select richtext from [text] where sno = '" + sno + "'";
            SqlDataAdapter da = new SqlDataAdapter(sql, connString);
            DataSet ds = new DataSet();
            da.Fill(ds,"text");
            CloseDB();
            return ds.Tables["text"];
        }

        /// <summary>
        /// push新备忘
        /// </summary>
        /// <param name="sno"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        public bool push(string sno, string text)
        {
            if (!ConnDB())
                return false;
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "insert into [text] values('" + text + "', '" + sno + "')";
            cmd.ExecuteNonQuery();
            return CloseDB();
        }
        
        /// <summary>
        /// del
        /// </summary>
        /// <param name="sno"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        public bool delete(string sno, string text)
        {
            if (!ConnDB())
                return false;
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "delete from [text] where sno = '" + sno + "' and richtext = '" + text + "'";
            cmd.ExecuteNonQuery();
            return CloseDB();
        }

        /// <summary>
        /// 更新备忘
        /// </summary>
        /// <param name="sno"></param>
        /// <param name="text"></param>
        /// <returns></returns>
        public bool update(string sno, string text, string oralText)
        {
            if (!ConnDB())
                return false;
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "update [text] set richtext = '" + text + "' where sno = '" + sno + "' and richtext = '" +
                              oralText + "'";
            cmd.ExecuteNonQuery();
            return CloseDB();
        }
        

        /// <summary>
        /// student实体类
        /// </summary>
        class student
        {
            private string sno;
            private string password;

            public void set_sno(string sno)
            {
                this.sno = sno;
            }

            public void set_password(string password)
            {
                this.password = password;
            }

            public string get_sno()
            {
                return this.sno;
            }

            public string get_password()
            {
                return this.password;
            }
        }
        
        
    }
}